﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace BuyLocalBonds
{
    public class SqlBean
    {
        public SqlBean()
        {
        }

        public DataSet GetBondsForPurchase()
        {
            DataSet ds = new DataSet();
            SqlConnection con = setupConnection();

            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM tblBond", con);

            da.Fill(ds, "tblBond");
            return ds;
        }

        public DataSet SearchBondsForPurchase(string bondName, string cusip, string issuer, double CouponLow, double CouponHigh, double CurrentLow, double CurrentHigh, double ParLow, double ParHigh, double YTMlow, double YTMhigh, double PriceLow, double PriceHigh)
        {   //string RatingLow, string RatingHigh
            //, double MaturityLow, double MaturityHigh
            //
            //
          
            DataSet ds = new DataSet();
            SqlConnection con = setupConnection();
            string sql = "Select * from tblBond_2 where bondName like @bondName or CUSIP like @cusip or issuer like @issuer or coupon between @couponlow and @couponhigh or currentYield between @currentlow and @currenthigh or parVal between @ParLow and @ParHigh or YTM between @YTMlow and @YTMhigh or price between @PriceLow and @PriceHigh";
            
            SqlCommand cmdBond = new SqlCommand(sql, con);

            cmdBond.Parameters.AddWithValue("@cusip", cusip); // add more parameters
            cmdBond.Parameters.AddWithValue("@bondName", bondName);
            cmdBond.Parameters.AddWithValue("@issuer", issuer);
            //cmdBond.Parameters.AddWithValue("@ratinglow", RatingLow);
            //cmdBond.Parameters.AddWithValue("@ratinghigh", RatingHigh);
            cmdBond.Parameters.AddWithValue("@couponlow", CouponLow);
            cmdBond.Parameters.AddWithValue("@couponhigh", CouponHigh);
            cmdBond.Parameters.AddWithValue("@currentlow", CurrentLow);
            cmdBond.Parameters.AddWithValue("@currenthigh", CurrentHigh); 
            //cmdBond.Parameters.AddWithValue("@MaturityLow", MaturityLow);
            //cmdBond.Parameters.AddWithValue("@MaturityHigh", MaturityHigh);
            cmdBond.Parameters.AddWithValue("@ParLow", ParLow);
            cmdBond.Parameters.AddWithValue("@ParHigh", ParHigh);
            cmdBond.Parameters.AddWithValue("@YTMlow", YTMlow);
            cmdBond.Parameters.AddWithValue("@YTMhigh", YTMhigh);
            cmdBond.Parameters.AddWithValue("@PriceLow", PriceLow);
            cmdBond.Parameters.AddWithValue("@PriceHigh", PriceHigh);

            SqlDataAdapter da = new SqlDataAdapter(cmdBond);
            da.Fill(ds, "tblBond_2");
            return ds;
        }

        /// <summary>
        /// Establishes an sql connection with the BLB database and returns the connection object
        /// </summary>
        private SqlConnection setupConnection()
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True");

            return con;
        }

        /// <summary>
        /// Returns true only if buying is successful.
        /// </summary>
        /// <param name="cusip"></param>
        /// <param name="qty"></param>
        /// <param name="custID"></param>
        /// <param name="traderID"></param>
        /// <param name="qtyAvail"></param>
        /// <returns></returns>
        public bool BuyBond(string cusip, int qty, string custID, string traderID, int qtyAvail)
        {
            int remQty = qtyAvail - qty;
            int tempNum; // for tryparse
            //string[] tempStr = { "", "" };

            if (remQty < 0 || cusip == null || cusip == "" || qty <= 0 || !Int32.TryParse(custID, out tempNum) || !Int32.TryParse(traderID, out tempNum))
            {
                return false;
            }

            ExecuteSqlBuyTransaction(cusip, qty, remQty, custID, traderID);

            return true;
        }

        #region BuyTransaction
        private void ExecuteSqlBuyTransaction(string cusip, int qty, int remQty, string custID, string traderID)
        {
            SqlConnection connection = setupConnection();

            connection.Open();

            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = connection.BeginTransaction("SampleTransaction");

            // Must assign both transaction object and connection 
            // to Command object for a pending local transaction
            command.Connection = connection;
            command.Transaction = transaction;

            try
            {
                insertIntoBondOrderTable(command, qty, custID, traderID);
                command.ExecuteNonQuery();

                updateQuantityInBondTable(cusip, remQty, command);
                command.ExecuteNonQuery();

                // Attempt to commit the transaction.
                transaction.Commit();
                connection.Close();
                Console.WriteLine("Both statements are committed to database.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  Message: {0}", ex.Message);

                // Attempt to roll back the transaction. 
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred 
                    // on the server that would cause the rollback to fail, such as 
                    // a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                    Console.WriteLine("  Message: {0}", ex2.Message);
                }
            }

        }

        private void insertIntoBondOrderTable(SqlCommand command, int qty, string custID, string traderID)
        {
            command.CommandText = "INSERT INTO tblBondOrder (qty, custID, traderID) VALUES (@quantity, @custID, @traderID)";

            command.Parameters.AddWithValue("@quantity", qty);
            command.Parameters.AddWithValue("@custID", custID);
            command.Parameters.AddWithValue("@traderID", traderID);
        }

        private void updateQuantityInBondTable(string cusip, int remQty, SqlCommand command)
        {
            command.CommandText = "UPDATE tblBond SET qty=" + remQty + " WHERE CUSIP=@cusip";

            command.Parameters.AddWithValue("@cusip", cusip);
        }

        #endregion BuyTransaction

    }
}
